package fw.helpers;

import static fw.Config.DB_NAME;
import static fw.Config.DB_PASSWORD;
import static fw.Config.DB_USER;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import fw.ApplicationManager;

public class DBHelper {

	private final ApplicationManager app;
	private static Logger LOG = LoggerFactory.getLogger(DBHelper.class);

	// Connection
	private Connection connection;
	// Object for queries to DB
	public Statement statement;
	// Query result
	ResultSet queryResult;

	/**
	 * Constructor
	 */
	public DBHelper(ApplicationManager p_app) {
		app = p_app;
		connect(DB_NAME);
	}

	/**
	 * Connect to DB
	 * 
	 * @param p_DBName
	 */
	private void connect(String p_DBName) {
		try {
			LOG.debug("Connecting to DB " + p_DBName + "...");

			// Start JDBC Driver
			DriverManager.registerDriver(new com.mysql.jdbc.Driver());
			// Connect to DB
			connection = DriverManager.getConnection("jdbc:mysql://localhost/" + p_DBName
					+ "?zeroDateTimeBehavior=convertToNull", DB_USER, DB_PASSWORD);
			// Query
			statement = connection.createStatement();
			// Query result
			queryResult = null;

		} catch (SQLException ex) {
			showError(ex);
		} catch (Exception ex) {
			LOG.error(ex.toString());
		}
	}

	/**
	 * Disconnect from DB
	 */
	public void disconnect() {
		try {
			// Close connection
			queryResult.close();
			connection.close();
		} catch (SQLException ex) {
			showError(ex);
		} catch (Exception ex) {
			LOG.error(ex.toString());
		}
	}

	/**
	 * Send query for SELECT
	 * 
	 * @param query
	 * @return
	 */
	public ResultSet querySelect(String query) {
		ResultSet queryResult = null;

		try {
			LOG.debug("Send query to DB: " + query);
			queryResult = statement.executeQuery(query);
		} catch (SQLException ex) {
			showError(ex);
		} catch (Exception ex) {
			LOG.error(ex.toString());
		}

		return queryResult;
	}

	/**
	 * Send query for DROP TABLE or DATABASE, INSERT into TABLE, UPDATE TABLE, DELETE from TABLE
	 * 
	 * @param query
	 * @return
	 */
	public void query(String query) {
		try {
			LOG.debug("Send query to DB: " + query);
			statement.executeUpdate(query);
		} catch (SQLException ex) {
			showError(ex);
		} catch (Exception ex) {
			LOG.error(ex.toString());
		}
	}

	protected void showError(SQLException ex) {
		LOG.error("SQL Error");

		while (ex != null) {
			LOG.error("SQLState: " + ex.getSQLState() + " ErrorCode: " + ex.getErrorCode() + " Message: "
					+ ex.getMessage());
			ex = ex.getNextException();
		}
	}

	public boolean isConnected() {
		try {
			return (connection != null && connection.isValid(0));
		} catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}

	}

	/**
	 * Get count of rows
	 * 
	 * @param p_queryResult
	 * @return
	 */
	public int getRowCount(ResultSet p_queryResult) {
		int r_rowCount = 0;

		try {
			if (p_queryResult.last()) {
				r_rowCount = queryResult.getRow();
				p_queryResult.beforeFirst();

				return r_rowCount;
			}
		} catch (SQLException ex) {
			LOG.warn("Cannot get count of row: " + ex.toString());
		}

		return r_rowCount;
	}
}